/**
 * 
 */
package data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

import edu.ipfw.fitness.MembershipDurationType;
import edu.ipfw.fitness.MembershipType;
import edu.ipfw.fitness.PatronIdListType;
import edu.ipfw.fitness.PatronType;
import edu.ipfw.fitness.PatronVisitType;
import edu.ipfw.services.patron.PatronNotFound;
import edu.ipfw.services.patron.PatronNotFoundFault;

/**
 * @author Angie
 *
 */
public class DbPatron {
	Connection conn = null;
	
	public DbPatron() {
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/DWWIG_Project", "root", "admin");

		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}

	
	}
	
	public void closeDbConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			// handle any errors
			e.printStackTrace();
		}
	}
	
	public boolean addPatron(PatronType p) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "insert into Patron (patronid, lastname, firstname, email, membershiptype, membershipduration, expirationdate, checkedin) values (";
		sql += "'" + p.getId() + "', '" + p.getLastName() + "', '" + p.getFirstName() + "', '" + p.getEmail() +
		"', (select id from pmembership where name = '" + p.getMembershipType() + "'), '"  + p.getMembershipDuration() + "', '" 
		+ df.format(p.getExpirationDate().toGregorianCalendar().getTime()) +  "', " + p.isCheckedIn() + ");";
		try {
			Statement s = conn.createStatement();
			s.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public PatronType getPatron(String id) throws PatronNotFoundFault {
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		PatronType p = new PatronType();
		GregorianCalendar cal = new GregorianCalendar();
		GregorianCalendar cal1 = new GregorianCalendar();
		String sql = "Select * from patron where patronid = '" + id + "'";
		try {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery(sql);
		if (rs.next()) {
		do {	
				
			p.setId(id);
			p.setLastName(rs.getString("lastname"));
			p.setFirstName(rs.getString("firstname"));
			p.setEmail(rs.getString("email"));
			p.setMembershipDuration(MembershipDurationType.fromValue(rs.getString("membershipduration")));
			int memType = rs.getInt("membershiptype");
			Statement s1 = conn.createStatement();
			ResultSet rs1 = s1.executeQuery("select name from pmembership where id = '" + memType + "'");
			while(rs1.next()) {
				p.setMembershipType(MembershipType.fromValue(rs1.getString(1)));
			}
			p.setCheckedIn(rs.getBoolean("checkedin"));
			cal.setTime(rs.getDate("expirationdate"));
			p.setExpirationDate(DatatypeFactory.newInstance().newXMLGregorianCalendar(cal));
			if (rs.getDate("lastcheckindate") != null) {
				String datetime = rs.getString("lastcheckindate");
			cal1.setTime(df1.parse(datetime));
			p.setLastCheckInDate(DatatypeFactory.newInstance().newXMLGregorianCalendar(cal1));
			p.getLastCheckInDate().setHour(cal1.get(Calendar.HOUR_OF_DAY));
			p.getLastCheckInDate().setMinute(cal1.get(Calendar.MINUTE));
			p.getLastCheckInDate().setSecond(cal1.get(Calendar.SECOND));
			}
			
		}while(rs.next());
		} else {
			throwPatronNotFoundFault();
		}
		return p;
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (DatatypeConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}
	public boolean update(PatronType p) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "update patron set membershipduration = '" + p.getMembershipDuration() +"', expirationdate = '" 
		+ df.format(p.getExpirationDate().toGregorianCalendar().getTime()) + "', lastcheckindate = '" +
		df1.format(p.getLastCheckInDate().toGregorianCalendar().getTime()) + "', checkedin = " + p.isCheckedIn()
		+ " where patronid = '" + p.getId() + "'";
		
		Statement s;
		try {
			s = conn.createStatement();
			s.execute(sql);
		return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	public void addVisit(PatronVisitType pv, String id) {
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql = "insert into pvisithistory(pid, checkintime, checkouttime) values ('" + id + "', '" 
		+ df1.format(pv.getCheckInTime().toGregorianCalendar().getTime()) + "', '" 
		+ df1.format(pv.getCheckOutTime().toGregorianCalendar().getTime())+ "');";
		try {
			Statement s = conn.createStatement();
			s.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public int getVisitForDateRange(javax.xml.datatype.XMLGregorianCalendar startDateTime,
			javax.xml.datatype.XMLGregorianCalendar endDateTime,
			java.lang.String id) throws PatronNotFoundFault {
		int visit = 0;
		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql;
		if (id == null) {
		sql = "Select count(*) from pvisithistory where checkintime between '" 
		+ df1.format(startDateTime.toGregorianCalendar().getTime()) + "' and '" + df1.format(endDateTime.toGregorianCalendar().getTime()) + "'";
		} else {
			sql = "Select count(*) from pvisithistory where pId = '" 
				+ id + "' and checkintime between '" 
			+ df1.format(startDateTime.toGregorianCalendar().getTime()) + "' and '" + df1.format(endDateTime.toGregorianCalendar().getTime()) + "'";
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
				do {
					visit = rs.getInt(1);
				} while (rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return visit;
	}
	
	public int getVisitForToday(String id) throws PatronNotFoundFault {
		int visit = 0;
		String sql;
		if (id == null) {
		sql = "Select count(*) from pvisithistory where date(checkintime) = curdate()";
		} else {
			sql = "Select count(*) from pvisithistory where pId = '" 
				+ id + "' and date(checkintime) = curdate()";
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
				do {
					visit = rs.getInt(1);
				} while (rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return visit;
	}

	/**
	 * @param expirationDate
	 * @return
	 */
	public PatronIdListType getMembersForExpDate(
			XMLGregorianCalendar expirationDate) {
		// TODO Auto-generated method stub
		PatronIdListType patrons = new PatronIdListType();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "Select patronid from patron where expirationdate = '" + df.format(expirationDate.toGregorianCalendar().getTime()) + "'";
		try {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery(sql);
		while (rs.next()) {
			patrons.getList().add(rs.getString(1));
		}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return patrons;
	}

	/**
	 * @param membershipType
	 * @return
	 */
	public PatronIdListType getMembersForType(MembershipType membershipType) {
		PatronIdListType patrons = new PatronIdListType();
		String sql = "Select patronid from patron where membershiptype = (select id from pmembership where name = '" + membershipType + "')";
		try {
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery(sql);
		while (rs.next()) {
			patrons.getList().add(rs.getString(1));
		}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return patrons;
	}
	
	public long getAvgDuration(String id) throws PatronNotFoundFault {
		long visit = 0;
		String sql;
		if (id == null) {
		sql = "Select avg(timestampdiff(second, checkintime,checkouttime)) from pvisithistory";
		} else {
			sql = "Select avg(timestampdiff(second, checkintime,checkouttime)) from pvisithistory where pId = '" 
				+ id + "'";
		}
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if (rs.next()) {
				do {
					visit = rs.getLong(1);
				} while (rs.next());
			} else {
				throwPatronNotFoundFault();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return visit;
		
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean remove(String id) throws PatronNotFoundFault {
		// TODO Auto-generated method stub
		getPatron(id);
		String sql = "delete from pvisithistory where pid = '" + id + "'";
		try {
		Statement s = conn.createStatement();
		s.execute(sql);
		sql = "delete from patron where patronid = '" + id + "'";
		s.execute(sql); 
		return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	private void throwPatronNotFoundFault()
	throws PatronNotFoundFault {
PatronNotFound error = new PatronNotFound();
error.setPatronNotFound("Patron was not found.");
throw new PatronNotFoundFault("PatronNotFound", error);
}
	
}
